In the sample application, both the Return Set and the
Query Criteria controls are presented in the main application form. These
controls are actually two instances of the same control placed on this
form with a property set to determine what part of the query is presented
and edited.
Operation of both panel modes is documented in the chapter on End User Query Help, the source of which is provided in the Visual Studio project, allowing you to modify and/or incorporate the help text for the query UI into your application's help system.
Using the Query UI, you can perform the following functions:
Add or delete fields to be included in the return set
Set Aggregation (sum count, avg, etc..) for one or more fields
Set the order of the returned values by dragging the bar on the right of each row
Set the sort order of the returned data
Limit the number of rows to be returned by the query
Turn on or off duplicate row elimination (Select Distinct)
Add or delete criteria that filters the rows returned by the query (the where clause)
Set criteria to user entered values
Set criteria to user prompted values at query run time
Set criteria to special function results
Set criteria to the values of other fields in the query
Set criteria to scalar values retrieved from the database (as expression fields).
Group criteria lines and apply "And", "Or", and "Not" conjunctions to each group.
Apply aggregation to criteria which promotes the criteria line from a "Where" condition to a "Having" filter.
Use the buttons at the bottom of the form to execute and test your query. A query may also be executed directly by your host application without presenting the Query User Interface. This may be done by referencing the cQuery.SQL property. You may also obtain individual clauses within the query by specifying the clause that you want such as 'Select', 'From', 'Where', 'Group By', 'Having' or 'Sort'.
When running a query, you may specify whether to ignore invalid SQL from partially constructed UI lines. This option is setable via the option check box at the bottom left of the form. If the query is loaded to the UI when it is run, invalid lines change color on the UI to indicate an issue.
Finally, you may further manage and create new queries by selecting the appropriate sub-menu item on the top menu of the sample application. Each query that is created is assigned a GUID identifier. You may use that GUID to file and/or reference the query to support some of the more advanced functionality of SQL Designer.
For example, you may build a more complex schema that partially consists of one or more tables that are themselves constructed by queries. In simpler cases, you would define those tables by assigning the static SQL for them directly. In more complex cases however, you may need user's to specify criteria for those queries via user prompts or you may need the criteria to be determined dynamically by a value function. In these cases, you would define those tables by assigning an SQL Designer query definition to the table. This is done by specifying the GUID of the query that should be used to create the "nested table". SQL Designer would then request the query definition be supplied by your host application via an event which references the required query using its GUID.